#!/bin/env ruby
# encoding: utf-8

require 'roo'

module ExcelReader

  def self.open_spreadsheet(file)
    case File.extname(file)
      when '.csv' then Roo::Csv.new(file, nil, :ignore)
      when '.xls' then Roo::Excel.new(file)
      when '.xlsx' then Roo::Excelx.new(file, nil, :ignore)
      else raise "File type unknown: #{file}"
    end
  end

  def self.read_cartasur_questionnaire(file="#{Rails.root}/public/dumps/cartasur/ResultsQestionnaire.xlsx")
    begin
      reader = open_spreadsheet(file)
      puts "opened file [#{file}] successfully.. "

    rescue Exception => exc
      puts "Error: a problem occurred when opening the file. details: #{exc.message}"
      return
    end

    reader.default_sheet = reader.sheets[0] #Mention the sheet number

    header = reader.row(2)

    #map unames to users currently
    (3..reader.last_row).each do |line| #start and end of row

      row_as_array = [header, reader.row(line)].transpose
      row = Hash[row_as_array]

      next if row["login"].nil?

      #strip whitespaces
      userstr = row['login'].gsub(/\s+/, '')
      user = Cartasur::User.find_by_login(userstr)

      next if user.nil?

      user.surname = row["Nom"]
      user.other_names = row["Prenom"]
      user.email2 = row["Email"]

      if row["etes-vous"].try(:downcase) =~ /femme/
        user.gender = 'F'
      else
        user.gender = 'M'
      end

      user.save

      puts "saved info for [#{user.login}] to database "

    end

    questions_row = reader.row(2)

    (5..reader.last_column).each do |idx|

      next if questions_row[idx].blank?

      question = Cartasur::Question.new

      question.text = questions_row[idx]
      question.details = questions_row[idx]
      question.number = idx - 4
      question.question_type =  Cartasur::Question::QUESTION_TYPES[:questionnaire]
      question.save

      #save the answers
      (3..reader.last_row).each do |line| #start and end of row

        begin
          row_as_array = [header, reader.row(line)].transpose
          row = Hash[row_as_array]

          #strip whitespaces
          userstr = row['login'].gsub(/\s+/, '')
          user = Cartasur::User.find_by_login(userstr)

          answer = Cartasur::Answer.new
          answer.answer_type = "qn_usr_" << user.id.try(:to_s) #survey answer uses answer_type as surveybyuser_id
          answer.question_id = question.id
          answer.protocol_id = 0
          answer.value = row[question.text]

          answer.save
        rescue Exception=> ex
          logger.error "Cartasur: Error when importing questionnaire values from Excel. Details: " << ex.message unless logger.nil?
          puts "Error: a problem occurred when processing answers. details: #{ex.message}"
        end
      end

    end

    puts "..finished processing questionnaire file"
  end

end